BigQueryの行レベルのアクセスポリシーを試してみる
2024.09.11はじめに
データアナリティクス事業本部のkobayashiです。
BigQueryでは行レベルのアクセスコントロールを行うことが可能です。行レベルのアクセスコントロールではユーザーに基づいてレコードのフィルタリングを行えます。今回はこの機能を試してみたのでまとめます。
BigQueryの行のレベルアクセスコントロール
行レベルのセキュリティは、BigQueryテーブル内の特定の行へのアクセスをユーザーの資格条件に基づいて制御する機能です。これにより、1つのテーブル内でもユーザーごとに閲覧可能なデータを細かく制限できます。
アクセスコントロールはテーブルに対して行レベルのアクセスポリシーを作成します。このポリシーは、特定のユーザーやグループに対して条件に合致する行のみを表示するフィルターとして機能し、他のレベルのアクセス制御（プロジェクト、データセット、テーブル、列レベル）と併せて使うことも可能です。
BigQueryの列レベルのアクセスコントロールを試してみる
下準備
それでは実際に行レベルのアクセスコントロールを試してみたいと思います。
対象となるテーブルはBigQuery一般公開データセットの Google トレンド データセット のinternational_top_termsテーブルを使います。
CREATE TABLE `{プロジェクトID}`.access_control.international_top_terms
AS
SELECT *
FROM `bigquery-public-data.google_trends.international_top_terms`
| refresh_date | region_name | region_code | score | rank | country_name | country_code | term | week |
|---|---|---|---|---|---|---|---|---|
| 2024-07-23 | Hokkaido Prefecture | JP-01 | 21 | 24 | Japan | JP | 東京ガス | 2024-07-07 |
| 2024-07-23 | Hokkaido Prefecture | JP-01 | 67 | 19 | Japan | JP | アシックス | 2024-07-07 |
| 2024-08-19 | Hokkaido Prefecture | JP-01 | 6 | 2 | Japan | JP | 和田アキ子 | 2024-07-07 |
| 2024-07-27 | Hokkaido Prefecture | JP-01 | 32 | 16 | Japan | JP | LIFE | 2024-07-07 |
| 2024-08-08 | Hokkaido Prefecture | JP-01 | 19 | 17 | Japan | JP | 日経平均 | 2024-07-07 |
行レベルのアクセスポリシーを作成
このテーブルに対して行レベルのアクセスポリシーを作成していきます。
CREATE ROW ACCESS POLICY country_jp_filter
ON access_control.international_top_terms
GRANT TO ('serviceAccount:bigquery-1@{ プロジェクト ID }.iam.gserviceaccount.com')
FILTER USING (country_code = 'JP');
行レベルのアクセスポリシーはCREATE ROW ACCESS POLICY statementで作成します。
- アクセスポリシー名: テーブルでユニークにする必要がある
GRANT TO: 行レベルのアクセスポリシーを適用するメンバーを指定する。指定できるのはuser,serviceAccount,group,domain,allAuthenticatedUsers,allUsersで、複数指定する場合はカンマ,で区切るFILTER USING: SQLのWHERE句で使う表現（一部制限あり）を記述する
CREATE ROW ACCESS POLICY DDL ステートメント リファレンス
ではこの状態でクエリを実行してみます。
> SELECT country_name, country_code, count(*) as cnt
FROM access_control.international_top_terms
GROUP BY 1, 2;
| country_name | country_code | cnt |
| :--- | :--- | :--- |
| Japan | JP | 155147 |
行レベルのアクセスポリシーが適用されてcountry_code = 'JP'のレコードだけしか取得できなくなっています。
行レベルのアクセスポリシーはAllowリストなのでテーブルに行レベルのアクセスポリシーが設定されるとアクセスポリシーに設定されていないユーザーは一切レコードが取得できなくなるため管理者など全てのレコードを読み取る必要のあるユーザーにはそのポリシーを作成する必要があります。
CREATE ROW ACCESS POLICY admin_filter
ON access_control.international_top_terms
GRANT TO (
'user:xxxx@example.com',
'serviceAccount:bigquery-admin@{ プロジェクト ID }.iam.gserviceaccount.com'
)
FILTER USING (TRUE);
複数のポリシーを組み合わせてみる
同じメンバーに別のポリシーを作成して動作を確認してみます。
CREATE ROW ACCESS POLICY score8090_filter
ON access_control.international_top_terms
GRANT TO ('serviceAccount:bigquery-1@{ プロジェクト ID }.iam.gserviceaccount.com')
FILTER USING (score BETWEEN 80 AND 90);
ではこの状態でクエリを実行してみます。
> SELECT country_name, country_code, count(*) as cnt
FROM access_control.international_top_terms
GROUP BY 1, 2;
| country\_name | country\_code | cnt |
| :--- | :--- | :--- |
| Argentina | AR | 693 |
| Austria | AT | 261 |
| France | FR | 869 |
| Israel | IL | 194 |
| Romania | RO | 854 |
| Sweden | SE | 732 |
| Australia | AU | 250 |
| Japan | JP | 155147 |
| Brazil | BR | 969 |
| Canada | CA | 360 |
| Switzerland | CH | 397 |
...
このように同じメンバーに複数のポリシーを作成するといずれかのポリシーのFilter条件に一致するレコードが全て取得できます。
全ての条件に一致するレコードのみにアクセスさせたい場合は以下のように1つのポリシーに条件をまとめて記述して作成する必要があります。
CREATE ROW ACCESS POLICY and_filter
ON access_control.international_top_terms
GRANT TO ('serviceAccount:bigquery-2@{ プロジェクト ID }.iam.gserviceaccount.com')
FILTER USING (country_code = 'JP' AND score BETWEEN 80 AND 90);
この状態でbigquery-2@{ プロジェクト ID }.iam.gserviceaccount.comとしてクエリを実行してみます。
> SELECT country_name, country_code, count(*) as cnt
FROM access_control.international_top_terms
GROUP BY 1, 2;
| country\_name | country\_code | cnt |
| :--- | :--- | :--- |
| Japan | JP | 1991 |
このように取得できるレコードが制限されていることがわかります。
行レベルのアクセスポリシー一覧
テーブルに作成されている行レベルのアクセスポリシーを確認するにはshellでbqコマンドを実行する必要があります。クエリでの取得はできないため注意が必要です。
$ bq ls --row_access_policies access_control.international_top_terms
Id Filter Predicate Grantees Creation Time Last Modified Time
-------------------- ------------------------------------------------- ------------------------------------------------------------------------------------------------------------ ----------------- --------------------
country_jp_filter country_code = 'JP' serviceAccount:bigquery-1@{ プロジェクト ID }.iam.gserviceaccount.com 09 Sep 04:19:09 09 Sep 04:19:09
admin_filter TRUE user:xxxx@example.com, serviceAccount:bigquery-admin@{プ ロジェクト ID }.iam.gserviceaccount.com 09 Sep 04:20:11 09 Sep 04:20:11
score8090_filter score BETWEEN 80 AND 90 serviceAccount:bigquery-1@{ プロジェクト ID }.iam.gserviceaccount.com 09 Sep 04:21:01 09 Sep 04:21:01
and_filter country_code = 'JP' AND score BETWEEN 80 AND 90 serviceAccount:bigquery-2@{ プロジェクト ID }.iam.gserviceaccount.com 09 Sep 04:23:26 09 Sep 04:23:26
行レベルのアクセスポリシーの削除
行レベルのアクセスポリシーの削除をしてみます。
特定の行レベルのアクセスポリシーを削除するにはDROP ROWを実行します。
DROP ROW ACCESS POLICY us_filter ON access_control.international_top_terms;
テーブルの全ての行レベルのアクセスポリシーを削除するにはDROP ALL ROWを実行します。
DROP ALL ROW ACCESS POLICIES ON access_control.international_top_terms;
まとめ
BigQueryのアクセス制御の一つである行レベルのアクセスコントロールを試してみました。ポリシーをSQLで記述できアクセスできるレコードの制御は通常のSQLのWhere句とほぼ同じ表現が使えるので非常に設定しやすいです。
最後まで読んで頂いてありがとうございました。
![[アップデート] Amazon QuickSight の Google BigQuery データセットでダイレクトクエリモードも使えるようになりました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-19e77b9a51519e08b94d0f688c125729/4f439c75e1ee56c70e315fa46fa45f81/amazon-quicksight)
